﻿using Inaction.DbSchame;
using Inaction.Mapping;
using Inaction.Rule;
using System;
using System.Data;
using System.Linq;
using System.Text;

namespace Inaction.Database.Schema
{
    public class OracleSchemaProvider : SchemaProviderBase
    {
        public override string ParaFix
        {
            get { return ":"; }
        }

        public string Owner { get; protected set; }

        internal override int MaxParaCount
        {
            get { return 1000; }
        }

        #region 构造
        public OracleSchemaProvider(string connectionstring)
            : base(connectionstring, SqlType.Oracle)
        {
        }

        protected override void Init()
        {
            var index = ConnectionString.Substring(ConnectionString.IndexOf("User ID") + 8);
            Owner = index.Substring(0, index.IndexOf(';')).ToUpper();
            base.Init();
        }

        internal override string ParseBatchSql(string sql)
        {
            return "BEGIN " + Environment.NewLine + sql + Environment.NewLine + " END;";
        }


        #endregion

        #region ' Sql Code'
        /*
         * TABLENAME:表名
         * COMMENTS:注释
         */
        protected override string SqlTables
        {
            get
            {
                return
                 @"SELECT SYS.ALL_TABLES.TABLE_NAME      AS TABLENAME,
                           SYS.ALL_TAB_COMMENTS.COMMENTS AS COMMENTS
                      FROM SYS.ALL_TABLES
                      JOIN SYS.ALL_TAB_COMMENTS
                        ON SYS.ALL_TAB_COMMENTS.TABLE_NAME = SYS.ALL_TABLES.TABLE_NAME
                       AND SYS.ALL_TABLES.OWNER = SYS.ALL_TAB_COMMENTS.OWNER
                       AND INSTR(SYS.ALL_TABLES.TABLE_NAME, '$') = 0
                       AND SYS.ALL_TABLES.OWNER = '" + Owner + "'";
            }
        }
        /*
         * VIEWNAME:视图名
         * SQL:视图Sql
         */
        protected override string SqlViews
        {
            get
            {
                return @"SELECT VIEW_NAME AS VIEWNAME
	                            ,TEXT     AS SQL
                            FROM SYS.ALL_VIEWS
                     WHERE SYS.ALL_VIEWS.OWNER = '" + Owner + "'";
            }
        }

        /*
         * TABLENAME:表名
         * COLUMNNAME:列名
         * DATATYPE:数据类型
         * LENGTH:长度
         * PRECISION:精度
         * SCALE:小数位数
         * NULLABLE:是否可为空
         * ORDINAL:序号
         * DEFAULTVALUE:默认值
         * COMMENTS:注释
         */
        protected override string SqlColumns
        {
            get
            {
                return
                 @"SELECT SYS.ALL_TAB_COLUMNS.TABLE_NAME      AS TABLENAME,
                           SYS.ALL_TAB_COLUMNS.COLUMN_NAME    AS COLUMNNAME,
                           SYS.ALL_TAB_COLUMNS.DATA_TYPE      AS DATATYPE,
                           SYS.ALL_TAB_COLUMNS.DATA_LENGTH    AS LENGTH,
                           SYS.ALL_TAB_COLUMNS.DATA_PRECISION AS PRECISION,
                           SYS.ALL_TAB_COLUMNS.DATA_SCALE     AS SCALE,
                           SYS.ALL_TAB_COLUMNS.NULLABLE       AS NULLABLE,
                           SYS.ALL_TAB_COLUMNS.COLUMN_ID      AS ORDINAL,
                           SYS.ALL_TAB_COLUMNS.DATA_DEFAULT   AS DEFAULTVALUE,
                           ALL_COL_COMMENTS.COMMENTS          AS COMMENTS
                      FROM SYS.ALL_TAB_COLUMNS
                      JOIN SYS.ALL_COL_COMMENTS
                        ON ALL_TAB_COLUMNS.TABLE_NAME = SYS.ALL_COL_COMMENTS.TABLE_NAME
                       AND ALL_TAB_COLUMNS.COLUMN_NAME = SYS.ALL_COL_COMMENTS.COLUMN_NAME
                       AND SYS.ALL_TAB_COLUMNS.OWNER = ALL_COL_COMMENTS.OWNER
                       AND INSTR(SYS.ALL_TAB_COLUMNS.TABLE_NAME, '$') = 0
                       AND SYS.ALL_TAB_COLUMNS.OWNER = '" + Owner + "'" + @"
                       ORDER BY SYS.ALL_TAB_COLUMNS.TABLE_NAME, SYS.ALL_TAB_COLUMNS.COLUMN_ID";
            }
        }

        /*
         * CONSTRAINTNAME:约束名
         * TABLENAME:表名
         * COLUMNNAME:列名 
         * REFERENCETABLE:外键引用的表
         * CONSTRAINTTYPE:约束类型:U:惟一索引,P:主键,R:外键
         */
        protected override string SqlConstraints
        {
            get
            {
                return @"SELECT COLS.CONSTRAINT_NAME      AS CONSTRAINTNAME
	                            ,COLS.TABLE_NAME          AS TABLENAME
	                            ,COLS.COLUMN_NAME         AS COLUMNNAME
	                            ,CONS2.TABLE_NAME         AS REFERENCETABLE
	                            ,CONS.CONSTRAINT_TYPE     AS CONSTRAINTTYPE
                            FROM ALL_CONSTRAINTS CONS
                            INNER JOIN ALL_CONS_COLUMNS COLS 
                                 ON (COLS.OWNER = '" + Owner + "'" + @")
	                            AND CONS.CONSTRAINT_NAME = COLS.CONSTRAINT_NAME
	                            AND CONS.CONSTRAINT_TYPE IN (
		                            'U'
		                            ,'P'
		                            ,'R'
		                            )
	                            AND CONS.OWNER = COLS.OWNER
                            LEFT JOIN ALL_CONSTRAINTS CONS2 
                                 ON CONS.R_CONSTRAINT_NAME = CONS2.CONSTRAINT_NAME
	                            AND CONS.OWNER = CONS2.OWNER";
            }
        }
        #endregion

        protected internal override string GetPageSql(Type type, int pageSize, int pageIndex)
        {
            var classInfo = ClassInfo.LoadInfo(type);
            return @"
SELECT {0} FROM 
(
    SELECT A.*, ROWNUM RN 
    FROM (SELECT {0} FROM " + classInfo.TableName + @" {1}) A 
    WHERE ROWNUM <= " + pageIndex * pageSize + @"
)
WHERE RN > " + pageSize * (pageIndex - 1);
        }


        #region 生成脚本
        public override string CreateScript(DbSchame.Table table)
        {
            string sql = " CREATE TABLE [dbo].[" + table.Name + "]";
            sql += "(" + Environment.NewLine;
            sql += string.Join("," + Environment.NewLine, table.Columns.Select(p => CreateColumnScript(p)));
            sql += string.Join("," + Environment.NewLine, table.ForeignKeys.Select(p => CreateForeignKeyScript(p)));
            sql += ")" + Environment.NewLine;
            sql += "GO" + Environment.NewLine;
            sql += string.Join("," + Environment.NewLine, table.Columns.Select(p => CreateDescriptionScript(p)));
            sql += CreateDescriptionScript(table) + Environment.NewLine;
            sql += "GO" + Environment.NewLine;
            sql += string.Join("," + Environment.NewLine, table.UniqueKeys.Select(p => CreateUniqueKeyScript(p)));
            sql += "GO" + Environment.NewLine;
            return sql;
        }

        private string CreateColumnScript(Column c)
        {
            string sql = c.FormatName + " " + c.DbType + " " + (c.Nullable ? "NULL" : "NOT NULL");
            if (c.IsPrimaryKey) sql += " PRIMARY KEY";
            return sql;
        }

        private string CreateForeignKeyScript(ForeignKey fk)
        {
            string sql = "CONSTRAINT [" + fk.Name + "] FOREIGN KEY ([" + fk.Column.Name + "])"
                + " REFERENCES  [" + fk.ReferenceTable.Name + "] ([" + fk.ReferenceTable.PrimaryKey.Name + "])"
                + " ON UPDATE NO ACTION ON DELETE NO ACTION";
            return sql;
        }

        private string CreateDescriptionScript(Column c)
        {
            string sql = @"EXEC sp_addextendedproperty @name=N'MS_Description',@value=N'" + c.DisplayName + "',@level0type=N'Schema',@level0name=N'dbo',@level1type=N'Table',@level1name=N'" + c.Table.Name + "',@level2type=N'Column',@level2name=N'" + c.Name + "';";
            return sql;
        }

        private string CreateDescriptionScript(Table t)
        {
            string sql = @"EXEC sp_addextendedproperty @name=N'MS_Description',@value=N'" + t.DisplayName + "',@level0type=N'Schema',@level0name=N'dbo',@level1type=N'Table',@level1name=N'" + t.Name + "';";
            return sql;
        }

        private string CreateUniqueKeyScript(UniqueKey uk)
        {
            string sql = @"CREATE UNIQUE NONCLUSTERED INDEX [" + uk.Name + @"] ON [dbo].[" + uk.Table.Name + "] (" + string.Join(",", uk.Columns.Select(p => p.FormatName)) + @")";
            return sql;
        }

        public override string CreateDataInsertSql(DbSchame.Table table)
        {
            var data = GetTableDatas(table);
            if (data != null && data.Rows.Count > 0)
            {
                StringBuilder sql = new StringBuilder();
                foreach (DataRow row in data.Rows)
                {
                    sql.AppendLine("INSERT INTO " + table.FormatName
                        + "(" + string.Join(",", data.Columns.Cast<DataColumn>().Select(p => NameFormater(p.ColumnName))) + ") " +
                      @"VALUES (" + string.Join(",", row.ItemArray.Select(p => p == DBNull.Value ? "NULL" : string.Format("N'{0}'", Inaction.Util.TypeConvert.ConvertToDbValue(p)))) + ");" + Environment.NewLine);
                }
                sql.AppendLine();
                return sql.ToString();
            }
            return string.Empty;
        }
        #endregion
    }
}
